package orest.common.util;
import java.util.*;
import java.sql.*;
import orest.common.valueobject.*;

/*
 * GetOption.java
 *
 * 
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */



/**
 *
 * @author jiaohong
 */
public class GetOption {
   // private DataBase db  = null;
    /** Creates a new instance of GetOption */
     public GetOption() {
    //    this.db = new DataBase();
    }
    private Tools tools = new Tools();
  public ArrayList  getLsbwList()  {	
               DataBase db = new DataBase();
	       ResultSet rs = null;
               Statement stmt = null;
               Connection conn = null;
               String strSQL = "select bwdm,bwmc from lsbw ";
               ArrayList myValList = new ArrayList();
              
      try {         
               // conn = db.getMyConnection();
                conn = db.getSysConnectionPool();
                stmt = conn.createStatement();
	        rs = stmt.executeQuery(strSQL);
                while (rs.next()) {
                       
                         myValList.add(new ProvinceVal(rs.getString(1), rs.getString(2))); 
                   }
                
	     } catch (Exception e) {
		   e.printStackTrace();
		} finally{
            /* close the prestatement */
            if(null != stmt){
                try{
                    stmt.close ();
                    stmt = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of GetOption " +
                            "and the function is getLsbwList, when" +
                            " close the pstmp, happen a new exception! " + e);
                }
            }
            
            if(null != rs){
                try{
                    rs.close ();
                    rs = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of GetOption " +
                            "and the function is getLsbwList, when" +
                            " close the pstmp, happen a new exception! " + e);
                }
            }
           
            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                }
                catch(Exception e){
                    System.out.println ("In the class of  GetOption and" +
                            "the function is getLsbwList, when " +
                            " close the rs, happen a new exception! " + e);
                }
            }
	
    }
                return myValList;
	}
  
  public ArrayList  getFieldOptionListBytnfn(String tbname,String fdname) {	
              DataBase db = new DataBase();
	       ResultSet rs = null;
               Statement stmt = null;
               Connection conn = null;
               String strSQL = "SELECT OptionValue,OptionName FROM T_FieldOption ";
               strSQL = strSQL + " WHERE ( TableName = '" + tbname + "' ) AND (FieldName = '" + fdname + "') ";  
               strSQL +=" ORDER BY OPTIONVALUE";
               ArrayList myValList = new ArrayList();
              
      try {         
              //  conn = db.getMyConnection();
                conn = db.getSysConnectionPool();
                stmt = conn.createStatement();
	        rs = stmt.executeQuery(strSQL);
                while (rs.next()) {
                        myValList.add(new FieldOptionVal(rs.getString(1), rs.getString(2))); 
                }
	     } catch (Exception e) {
		   e.printStackTrace();
		} finally{
            /* close the prestatement */
            if(null != stmt){
                try{
                    stmt.close ();
                    stmt = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of GetOption " +
                            "and the function is getFieldOptionListBytnfn, when" +
                            " close the pstmp, happen a new exception! " + e);
                }
            }
            
            if(null != rs){
                try{
                    rs.close ();
                    rs = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of GetOption " +
                            "and the function is getFieldOptionListBytnfn, when" +
                            " close the pstmp, happen a new exception! " + e);
                }
            }
           
            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                }
                catch(Exception e){
                    System.out.println ("In the class of  GetOption and" +
                            "the function is getFieldOptionListBytnfn, when " +
                            " close the rs, happen a new exception! " + e);
                }
            }
	
    }
                return myValList;
	}
  
  public ArrayList  getProvinceList()  {	
               DataBase db = new DataBase();
	       ResultSet rs = null;
               Statement stmt = null;
               Connection conn = null;
               String strSQL = "SELECT * FROM province ";


               ArrayList myValList = new ArrayList();
               //myValList = null;
               ProvinceVal myValObj = new ProvinceVal();
      try {         
               // conn = db.getMyConnection();
                conn = db.getSysConnectionPool();
                stmt = conn.createStatement();
	        rs = stmt.executeQuery(strSQL);
               
                while (rs.next()){
                        
                         myValList.add(new ProvinceVal(rs.getString("province_id"),rs.getString("province_name")));      
                }
             
                
	     } catch (Exception e) {
		   e.printStackTrace();
		} finally{
            /* close the prestatement */
            if(null != stmt){
                try{
                    stmt.close ();
                    stmt = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of GetOption " +
                            "and the function is getProvinceList, when" +
                            " close the pstmp, happen a new exception! " + e);
                }
            }
            
            if(null != rs){
                try{
                    rs.close ();
                    rs = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of GetOption " +
                            "and the function is getProvinceList, when" +
                            " close the pstmp, happen a new exception! " + e);
                }
            }
           
            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                }
                catch(Exception e){
                    System.out.println ("In the class of  GetOption and" +
                            "the function is getProvinceList, when " +
                            " close the rs, happen a new exception! " + e);
                }
            }
	
    }
               return myValList; 
  }
  
  public String  getOptionNameFromOptionValue(String tablename,String fieldname,String optionvalue) {	
               String retval = "";
               DataBase db = new DataBase();
	       ResultSet rs = null;
               PreparedStatement pstmt = null;
               Connection conn = null;
               String strSQL = "SELECT  OptionName FROM T_FieldOption where TableName=? and FieldName=? and OptionValue=? ";
               ArrayList myValList = new ArrayList();
              
      try {         
              //  conn = db.getMyConnection();
                conn = db.getSysConnectionPool();
                pstmt = conn.prepareStatement(strSQL);
		pstmt.setString(1,tablename);
                pstmt.setString(2,fieldname);
                pstmt.setString(3,optionvalue);
	        rs = pstmt.executeQuery();
                while (rs.next()) {
                     retval = rs.getString("OptionName"); 
                }
                
	     } catch (Exception e) {
		   e.printStackTrace();
		} finally{
            /* close the prestatement */
            if(null != pstmt){
                try{
                    pstmt.close ();
                    pstmt = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of GetOption " +
                            "and the function is getXxmcFromXxdm, when" +
                            " close the pstmp, happen a new exception! " + e);
                }
            }
            
            if(null != rs){
                try{
                    rs.close ();
                    rs = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of GetOption " +
                            "and the function is getXxmcFromXxdm, when" +
                            " close the pstmp, happen a new exception! " + e);
                }
            }
           
            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                }
                catch(Exception e){
                    System.out.println ("In the class of  GetOption and" +
                            "the function is getXxmcFromXxdm, when " +
                            " close the rs, happen a new exception! " + e);
                }
            }
	
    }
                return retval;
	}

public String  getProvinceNameFromProvinceID(String id) {	
               String retval = "";
               DataBase db = new DataBase();
	       ResultSet rs = null;
               PreparedStatement pstmt = null;
               Connection conn = null;
               String strSQL = "SELECT province_name FROM province where  province_id=? ";
               ArrayList myValList = new ArrayList();
              
      try {         
               // conn = db.getMyConnection();
                conn = db.getSysConnectionPool();
                pstmt = conn.prepareStatement(strSQL);
		pstmt.setString(1,id);
                rs = pstmt.executeQuery();
                while (rs.next()) {
                     retval = rs.getString("province_name"); 
                }
                
	     } catch (Exception e) {
		   e.printStackTrace();
		} finally{
            /* close the prestatement */
            if(null != pstmt){
                try{
                    pstmt.close ();
                    pstmt = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of GetOption " +
                            "and the function is getProvinceNameFromProvinceID, when" +
                            " close the pstmp, happen a new exception! " + e);
                }
            }
            
            if(null != rs){
                try{
                    rs.close ();
                    rs = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of GetOption " +
                            "and the function is getProvinceNameFromProvinceID, when" +
                            " close the pstmp, happen a new exception! " + e);
                }
            }
           
            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                }
                catch(Exception e){
                    System.out.println ("In the class of  GetOption and" +
                            "the function is getProvinceNameFromProvinceID, when " +
                            " close the rs, happen a new exception! " + e);
                }
            }
	
    }
                return retval;
	}


  public ArrayList  getZdxxList(String item)  {
               DataBase db = new DataBase();
	       ResultSet rs = null;
               Statement stmt = null;
               Connection conn = null;
               String strSQL = "select xxdm,xxmc from zdxx where zddm= '"+ item +"'";
               ArrayList myValList = new ArrayList();

      try {

                conn = db.getSysConnectionPool();
                stmt = conn.createStatement();
	        rs = stmt.executeQuery(strSQL);
                while (rs.next()) {

                         myValList.add(new ProvinceVal(rs.getString(1), rs.getString(2)));
                   }

	     } catch (Exception e) {
		   e.printStackTrace();
		} finally{
            /* close the prestatement */
            if(null != stmt){
                try{
                    stmt.close ();
                    stmt = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of GetOption " +
                            "and the function is getZdxxList, when" +
                            " close the pstmp, happen a new exception! " + e);
                }
            }

            if(null != rs){
                try{
                    rs.close ();
                    rs = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of GetOption " +
                            "and the function is getZdxxList, when" +
                            " close the pstmp, happen a new exception! " + e);
                }
            }

            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                }
                catch(Exception e){
                    System.out.println ("In the class of  GetOption and" +
                            "the function is getZdxxList, when " +
                            " close the rs, happen a new exception! " + e);
                }
            }

    }
                return myValList;
	}

     public String  getXxmcFromXxdm(String zddm,String xxdm) {
               String retval = "";
               DataBase db = new DataBase();
	           ResultSet rs = null;
               PreparedStatement pstmt = null;
               Connection conn = null;
               String strSQL = "select xxmc from zdxx where  zddm=? and xxdm=? ";
               ArrayList myValList = new ArrayList();

          try {

                conn = db.getSysConnectionPool();
                pstmt = conn.prepareStatement(strSQL);
                pstmt.setString(1,zddm);
                pstmt.setString(2,xxdm);
                rs = pstmt.executeQuery();
                while (rs.next()) {
                     retval = rs.getString("xxmc");
                }

	     } catch (Exception e) {
		   e.printStackTrace();
		} finally{
            /* close the prestatement */
            if(null != pstmt){
                try{
                    pstmt.close ();
                    pstmt = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of GetOption " +
                            "and the function is getXxmcFromXxdm, when" +
                            " close the pstmp, happen a new exception! " + e);
                }
            }

            if(null != rs){
                try{
                    rs.close ();
                    rs = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of GetOption " +
                            "and the function is getXxmcFromXxdm, when" +
                            " close the pstmp, happen a new exception! " + e);
                }
            }

            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                }
                catch(Exception e){
                    System.out.println ("In the class of  GetOption and" +
                            "the function is getXxmcFromXxdm, when " +
                            " close the rs, happen a new exception! " + e);
                }
            }

    }
                return retval;
	}


}